from odoo import api, fields, models, _


class ProductProductStockReport(models.Model):
    _name = 'product.product.stock.report'
    _description = "Product Product Report"
    _order = 'product_id'
    _auto = False

    partner_code = fields.Char('Partner Code')
    item_code = fields.Char('Item Code')
    product_id = fields.Many2one('product.product', string='Product')
    actual_quantity = fields.Float(string='Actual qty')
    stock_lot_id = fields.Many2one('stock.lot', string='Lot')
    s_week_of_year = fields.Char(string='Production Week Of Year')
    uom_id = fields.Many2one('uom.uom', related='product_id.uom_id', string='Unit of Measure')
    product_metre = fields.Float(string='Metres')
    storage_location_id = fields.Many2one('stock.location')

    def open_stock_lot(self):
        return {
            'name': _('Lot Form'),
            'view_mode': 'form',
            'view_id': self.env.ref('sb_stock_inherit.open_stock_lot_wizard_wizard_form_view').id,
            'res_model': 'open.stock.lot.wizard',
            'type': 'ir.actions.act_window',
            'target': 'new',
            'context': {
                'default_product_id': self.product_id.id,
                'default_stock_lot_id': self.stock_lot_id.id,
                'default_s_week_of_year': self.s_week_of_year
            }
        }

    @property
    def _table_query(self):
        return f"""
            WITH RECURSIVE
                location_explosion AS (SELECT id, ARRAY [id] AS location_path
                                       FROM stock_location
                                       WHERE barcode = 'WH-STOCK-FINISHED-PRODUCT'
            
                                       UNION ALL
            
                                       SELECT sl.id, le.location_path || sl.id
                                       FROM location_explosion le
                                                INNER JOIN stock_location sl ON le.id = sl.location_id
                                       WHERE sl.id <> ALL (le.location_path)),
                cic_filtered AS ( -- 重命名CTE避免名称冲突
                    SELECT rp.code  AS customer_code,
                           cic.code AS item_code,
                           pt.id    AS product_tmpl_id -- 改为关联模板ID
                    FROM product_template pt
                             LEFT JOIN product_category pc ON pt.categ_id = pc.id
                             LEFT JOIN (SELECT *,
                                               ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY id DESC) AS rn
                                        FROM customer_item_code -- 使用原始表名
                    ) cic ON cic.product_id = pt.id AND cic.rn = 1
                             LEFT JOIN res_partner rp ON cic.customer_id = rp.id
                    WHERE pc.code = 'CHP')
            
            SELECT ROW_NUMBER() OVER ()                                                                  AS ID,
                   pp.id                                                                                 AS product_id,
                   sl.id                                                                                 AS stock_lot_id,
                   sl.s_week_of_year,
                   SUM(sq.quantity)                                                                      AS actual_quantity,
                   CEIL(SUM(sq.quantity) / COALESCE(puva3.uom_value, 1)) * puva.uom_value * puva2.uom_value / 1000000 AS product_metre,
                   sq.location_id                                                                        AS storage_location_id,
                   cic.customer_code                                                                     AS partner_code,
                   cic.item_code
            FROM location_explosion le
                     INNER JOIN stock_quant sq ON sq.location_id = le.id
                     INNER JOIN stock_lot sl ON sq.lot_id = sl.id
                     INNER JOIN product_product pp ON sl.product_id = pp.id -- 改为INNER JOIN确保产品存在
                     LEFT JOIN cic_filtered cic ON cic.product_tmpl_id = pp.product_tmpl_id -- 通过模板ID关联
                     LEFT JOIN product_uom_value puva ON puva.product_id = pp.product_tmpl_id
                AND puva.uom_id IN (SELECT id FROM uom_uom WHERE code = 'SETW')
                     LEFT JOIN product_uom_value puva2 ON puva2.product_id = pp.product_tmpl_id
                AND puva2.uom_id IN (SELECT id FROM uom_uom WHERE code = 'SETL')
                     LEFT JOIN product_uom_value puva3 ON puva3.product_id = pp.product_tmpl_id
                AND puva3.uom_id IN (SELECT id FROM uom_uom WHERE code = 'SET')
            where sq.quantity > 0
            GROUP BY pp.id,
                     sl.id,
                     sl.s_week_of_year,
                     puva3.uom_value,
                     puva.uom_value,
                     puva2.uom_value,
                     sq.location_id,
                     cic.customer_code,
                     cic.item_code
            ORDER BY pp.id
        """
